import pandas as pd
import math

# 定义Haversine公式计算两点之间的距离
def calculate_distance(lat1, lon1, lat2, lon2):
    R = 6371000  # 地球半径，单位为米
    lat1_rad = math.radians(lat1)
    lon1_rad = math.radians(lon1)
    lat2_rad = math.radians(lat2)
    lon2_rad = math.radians(lon2)
    dlat = lat2_rad - lat1_rad
    dlon = lon2_rad - lon1_rad
    a = math.sin(dlat / 2) ** 2 + math.cos(lat1_rad) * math.cos(lat2_rad) * math.sin(dlon / 2) ** 2
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    distance = R * c
    return distance

# 指定输入文件路径
input_file_path1 = 'D:\\xlzx\\4G5G资料20220610\\结果文件.xlsx'
input_file_path2 = 'D:\\xlzx\\4G5G资料20220610\\2025\\站址存量表20250212.xlsx'

# 读取 Excel 文件
df1 = pd.read_excel(input_file_path1)
df2 = pd.read_excel(input_file_path2)

# 将经纬度列转换为浮点数类型
df1['经度'] = df1['经度'].astype(float)
df1['纬度'] = df1['纬度'].astype(float)
df2['站址经度'] = df2['站址经度'].astype(float)
df2['站址纬度'] = df2['站址纬度'].astype(float)

# 提取经纬度信息
start_points = df1[['经度', '纬度']].dropna().values
end_points = df2[['站址经度', '站址纬度']].dropna().values

# 初始化结果列表
results = []

# 遍历每个起点，找到最近的终点
for start_lat, start_lon in start_points:
    min_distance = float('inf')
    closest_end_index = None
    for i, (end_lat, end_lon) in enumerate(end_points):
        distance = calculate_distance(start_lat, start_lon, end_lat, end_lon)
        if distance < min_distance:
            min_distance = distance
            closest_end_index = i
    closest_end_row = df2.iloc[closest_end_index]
    results.append((start_lat, start_lon, closest_end_row['站址编码INTID'], min_distance))

# 更新原始文件
df1['最近站址编码INTID'] = [None] * len(df1)
df1['最短距离'] = [None] * len(df1)

for i, (start_lat, start_lon, closest_intid, min_distance) in enumerate(results):
    index = df1[(df1['经度'] == start_lat) & (df1['纬度'] == start_lon)].index[0]
    df1.at[index, '最近站址编码INTID'] = closest_intid
    df1.at[index, '最短距离'] = min_distance

# 保存更新后的文件
output_file_path = 'D:\\xlzx\\4G5G资料20220610\\2025\\主设备1223_更新.xlsx'
df1 = df1.astype(str)
df1.to_excel(output_file_path, index=False)

print(f"文件已保存到: {output_file_path}")